Loading Excel File to Data Warehouse 9

- In Data Flow Task ---> Drag and Drop Excel Source from Other Sources because we have our source in Excel File.

-  Add the Connection Manager for the Source ---> (1) Double Click to Excel Source ---> (2) In Connection Manager ---> (3) Choose New for Excel Connection Manager.

- Browse for Excel File Path ---> Click to Browse

- Browse for the Excel File ---> Click Open

- Click to (1) “First row has column names” ---> (2) Click OK

- Choose the Sheet that contains Data

(1) Click to Columns ---> (2) Choose all Columns ---> (3) Click OK


- Use Conditional Split to split the Null value

- Drag and drop the Conditional Split from Commons to Data Flow Task ---> Connect the Excel Source to Conditional Split by dragging the blue line from Excel Source to Conditional Split.


- Double Click to Conditional Split --->(1) Give “Null Records” in Output Name ---> (2) Give “ISNULL(ListName) || ISNULL(CampaignID) || ISNULL(CampaignTitle) || ISNULL(IsActive) || ISNULL(CreatedDate) || ISNULL(SentDate) || ISNULL(Email) || ISNULL(EmailOpenCount) || ISNULL(Status)” in Condition ---> (3) Give “NotNull Records” in Default Output Name ---> (4) Click OK.


- (1) (2) Drag and Drop 2 Multicast to Data Control Flow

- (3) (4) connect the Conditional Split to the Multicasts by dragging the NotNull Records to the left Multicast and dragging the Null Records to the right Multicast ---> (5) Click Ok.


- Use Data Conversion to convert the data

- Drag and Drop the Data Conversion from Common to Data Flow Task ---> Connect the Multicast from the left side to Data Conversion by dragging the blue line from the left Multicast to the data Conversion.

- Double Click to Data Conversion

- (1) Choose all the columns ---> (2) Change the Data Type to String ---> (3) Change the Length to 255 ---> (4) Click OK.


- (1) Drag and Drop OLE DB Destination to Data Flow Task because we need to load the data to SQL Database Table

- (2) Connect Data Conversion to OLE DB Destination by dragging blue line from Data Conversion to OLE DB Destination.


Double click to OLE DB Destination to create connection manager.



(1) In Connection manager ---> (2) Click to New ---> (3) Click to New .


- (1) In Server Name -> Choose Server Name ---> (2) In Select or enter database name -> Choose the database that we want to load the data to ---> (3) Click OK. 


(1) In Data Connection click to the connection manager we have just created ---> (2) Click OK.



In OLE DB Destination Editor -> In Name of the table or the view -> Click New.



(1) In CREATE TABLE change the name to MailChimp ---> (2) Delete all the Copy of _ columns and the last comma ---> (3) Click OK.


- Click to mappings



(1) Reconnect the columns to Copy of _ (these are the columns from Data Conversion) ---> (2) Click OK.



(1) Right click to the Package ---> (2) Click to Execute Package.



- The data is loaded from Excel Source to OLE DB Destination.